package com.cse.p2p.database;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
import java.util.Map;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 *
 * @author DUCMINHKHOI
 */
public class ClientDatabase {

    private static final String DATABASE_URL = "jdbc:sqlite:client.db";
    private static boolean isDatabaseOpen = false;
    private static Connection connection = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;

    /**
     * This method will open the Database client.db
     * consist of open connection, statement
     * This method is called only once at the beginning of the client's life
     * @throws SQLException
     * @throws Exception
     *
     */
    public static void openDatabase() throws SQLException, Exception{
        if (!isDatabaseOpen){
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection(DATABASE_URL);
            statement = connection.createStatement();
            isDatabaseOpen = true;
        }
    }

    /**
     * This method with close the Database
     * This method is called once at the end of the client's life
     */
    public static void closeDatabase(){
        try {
            connection.close();
        } catch (Exception e) {
        }

        isDatabaseOpen = false;
    }

    private static void closeResultSetAndStatment(){
        try {
            resultSet.close();
            statement.close();
        } catch (Exception e) {
        }
    }

    /**
     * This method will insert new row in File table
     * Using when read the DicFile (receiving from Server) the first time
     * @param fileName: name of the file
     * @param fileRealName: the real name of the file
     * @param fileID: the file's ID that written in the Server's database
     * @param fileSize: the file's Size
     * @param filePath: the file's Path
     * @param fileDescribe: the file's Describe
     * @param clientUpIP: the IP of the client upload this file to the server
     * @throws SQLException
     */
    public static void NewFile(String fileName, String fileRealName, int fileID,
            long fileSize, String filePath, String fileDescribe,
            String clientUpIP) throws SQLException {

        statement.executeUpdate(String.format("insert into file values(%d, '%s', '%s', %d, '%s', '%s', '%s');",
                fileID, fileName, fileRealName, fileSize, filePath, fileDescribe, clientUpIP));
        closeResultSetAndStatment();
    }

    /**
     * This method will insert new row in Piece table
     * Using when complete receiving a piece from other client
     * @param pieceID: the number order of the piece in the whole file
     * @param fileID: the file's ID that written in Server's database
     * @param hashkey: the hashkey (must in String type)
     * @throws SQLException
     */
    public static void NewPiece(int pieceID, int fileID, String hashkey) throws SQLException {

        statement.executeUpdate(String.format("insert into piece values(%d, %d, '%s');",
                pieceID, fileID, hashkey));

        closeResultSetAndStatment();
    }

    /**
     * This method will check whether a piece exists in Piece table
     * Using when Other client request for status of file
     * OR when this client have this piece of the whole file and don't want downloading this piece again
     * @param pieceID: the number order of the piece in the whole file
     * @param fileID: the file's ID that written in Server's database
     * @return true OR false: this piece in the Piece table or not
     * @throws SQLException
     */
    public static boolean checkPieceExist(int pieceID, int fileID) throws SQLException {
        boolean checkExist = true;

        resultSet = statement.executeQuery("select * from piece where pieceid = "+ pieceID +
                 " and fileid = "+ fileID +";");
        if (!resultSet.next()){
            checkExist = false;
        }
        closeResultSetAndStatment();

        return checkExist;
    }

    /**
     * This method will get the existing Piece related to given fileID in the piece table
     * Using when Server want to get list of piece ID from client's database
     * @param fileID: file's ID
     * @return ListOfPieceExist: type LinkedList
     * @throws SQLException
     */
    public static List getPiecesExist(int fileID) throws SQLException {
        List pieceIDList = new LinkedList();

        resultSet = statement.executeQuery("select * from piece where fileid = " + fileID + ";");
        while (resultSet.next()){
            pieceIDList.add(resultSet.getInt("pieceid"));
        }
        closeResultSetAndStatment();
        return pieceIDList;
    }

    /**
     * This method will return the path of a given file's ID from file table
     * @param fileID: file's ID
     * @return filePath: String of filePath
     * @throws SQLException
     */
    public static String getFilePath(int fileID) throws  SQLException{
        String filePath = null;
        resultSet = statement.executeQuery("select * from file where fileid = " + fileID +";");
        if (resultSet.next()){
            filePath = resultSet.getString("filepath");
        }
        closeResultSetAndStatment();
        return filePath;
    }

    /**
     * This method will return the fileName of given file's ID from file table
     * @param fileID: file's ID
     * @return fileName: String of fileName
     * @throws SQLException
     */
    public static String getFileName(int fileID) throws  SQLException{
        String fileName = null;
        resultSet = statement.executeQuery("select * from file where fileid = " + fileID +";");
        if (resultSet.next()){
            fileName = resultSet.getString("filename");
        }
        closeResultSetAndStatment();

        return fileName;
    }

    /**
     * This method will return the fileSize of given file's ID from file table
     * @param fileID: file's ID
     * @return fileSize: Long type of fileSize
     * @throws SQLException
     */
    public static long getFileSize(int fileID) throws  SQLException{
        long fileSize = 0;
        resultSet = statement.executeQuery("select * from file where fileid = " + fileID +";");
        if (resultSet.next()){
            fileSize = resultSet.getLong("filesize");
        }
        closeResultSetAndStatment();
        return fileSize;
    }

    /**
     * This method will return the fileDescribe of given file's ID from the file table
     * @param fileID: file's ID
     * @return fileDescribe: String of describe
     * @throws SQLException
     */
    public static String getFileDescribe(int fileID) throws  SQLException{
        String fileDescribe = null;
        resultSet = statement.executeQuery("select * from file where fileid = " + fileID +";");
        if (resultSet.next()){
            fileDescribe = resultSet.getString("filedescribe");
        }
        closeResultSetAndStatment();
        return fileDescribe;
    }

    /**
     * This method will return the fileRealName of a given file's ID from file table
     * @param fileID: file's ID
     * @return fileRealName: String of file real name
     * @throws SQLException
     */
    public static String getFileRealName(int fileID) throws  SQLException{
        String fileRealName = null;
        resultSet = statement.executeQuery("select * from file where fileid = " + fileID +";");
        if (resultSet.next()){
            fileRealName = resultSet.getString("filerealname");
        }
        closeResultSetAndStatment();
        return fileRealName;
    }

    /**
     * This method will return the ClientUPIP of a given file's ID from file table
     * @param fileID: file's ID
     * @return clientUpIP: String of IP of client upload this file to Server
     * @throws SQLException
     */
    public static String getClientUpIP(int fileID) throws  SQLException{
        String clientUpIP = null;
        resultSet = statement.executeQuery("select * from file where fileid = " + fileID +";");
        if (resultSet.next()){
            clientUpIP = resultSet.getString("clientupip");
        }
        closeResultSetAndStatment();
        return clientUpIP;
    }

    /**
     * This method will return the HashCode of given pieceID and fileID from the piece table
     * @param pieceID: the other number of this Piece in the whole file
     * @param fileID: the file's ID
     * @return hashcode: String of hashCode
     * @throws SQLException
     */
    public static String getPieceHashCode(int pieceID, int fileID) throws  SQLException{
        String hashCode = null;
        resultSet = statement.executeQuery("select * from piece where pieceid = " + pieceID
                +" and fileid = "+ fileID +";");
        if (resultSet.next()){
            hashCode = resultSet.getString("hashkey");
        }
        closeResultSetAndStatment();
        return hashCode;
    }
}
